package sdufe.lele.dao;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import sdufe.lele.model.Message;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


@Mapper
@Repository
public interface MessageMapper {

    int deleteByPrimaryKey(Integer messageid);

    int insert(Message record);

    @Insert("insert into message (messageType,messageBroker,messageMoney,messageOthers,messageLevel,messageUserPhone) " +
            "values (#{messagetype},#{messagebroker},#{messagemoney},#{messageothers},#{messagelevel},#{messageuserphone})")
    int insertSelective(Message record);

    Message selectByPrimaryKey(Integer messageid);

    int updateByPrimaryKeySelective(Message record);

    int updateByPrimaryKey(Message record);


    @Select("select * from message limit #{down},#{up}")
    @Results({
            @Result(id = true, column = "messageId", property = "messageid"),
            @Result(column = "messageCreateDate", property = "messagecreatedate"),
            @Result(column = "messageType",property = "messagetype"),
            @Result(column = "messageBroker",property = "messagebroker"),
            @Result(column = "messageMoney",property = "messagemoney"),
            @Result(column = "messageOthers",property = "messageothers"),
            @Result(column = "messageLevel",property = "messagelevel")

    })
    ArrayList<Message> selectByPage(@Param("down") Integer pageDown,@Param("up") Integer pageUp);

    @Select("select * from message  where messageUserPhone = #{phone} limit #{down},#{up}")
    @Results({
            @Result(id = true, column = "messageId", property = "messageid"),
            @Result(column = "messageCreateDate", property = "messagecreatedate"),
            @Result(column = "messageType",property = "messagetype"),
            @Result(column = "messageBroker",property = "messagebroker"),
            @Result(column = "messageMoney",property = "messagemoney"),
            @Result(column = "messageOthers",property = "messageothers"),
            @Result(column = "messageLevel",property = "messagelevel"),
            @Result(column = "messageUserPhone",property = "messageuserphone")

    })
    ArrayList<Message> selectByPagAndPhone(@Param("down") Integer pageDown,@Param("up") Integer pageUp,@Param("phone") String phone);

    @Select("update message set messageType = #{newName} where messageType = #{oldName}")
    int updatealltype(String oldName,String newName);

    @Select("select count(*) from message where messageUserPhone = #{userphone}")
    int countAllByPhone(@Param("userphone") String userphone);

    @Select("select count(*) from message where  DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(messageCreateDate) and  messageUserPhone = #{userphone}")
    int countByCurrentMonthAndPhone(@Param("userphone") String userphone);

    @Select("select count(*) from message ")
    int countAll();

    @Select("select count(*) from message where  DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(messageCreateDate)")
    int countByCurrentMonth();

    @Select("select DATE_FORMAT(messageCreateDate,'%Y%m') months,sum(messageMoney) sum,messageLevel from message where DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(messageCreateDate) and messageUserPhone = #{userphone}  group by months,messageLevel")
    ArrayList<HashMap<String,Object>> sumByMonthByPhone(@Param("userphone") String userphone);

    @Select("select DATE_FORMAT(messageCreateDate,'%Y%m') months,sum(messageMoney) sum,messageLevel from message where DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(messageCreateDate)   group by months,messageLevel")
    ArrayList<HashMap<String,Object>> sumByMonth();


    @Select("select sum(messageMoney) value,messageType name from message where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(messageCreateDate) and messageLevel = 2  group by messageType")
    ArrayList<HashMap<String,Object>> selectOutcomeGroupByType();



    @Select("select sum(messageMoney) value,messageType name from message where DATE_SUB(CURDATE(), INTERVAL 1 ${date}) <= date(messageCreateDate) and messageLevel = 2 and  messageUserPhone = #{userphone}  group by messageType")
    ArrayList<HashMap<String,Object>> selectOutcomeGroupByTypeAndPhone(@Param("userphone") String phone,@Param("date")String date);


    @Select("select messageType name,sum(messageMoney) value from message where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(messageCreateDate) and messageLevel = 1  group by messageType")
    ArrayList<HashMap<String,Object>> selectIncomeGroupByType();

    @Select("select messageBroker name,sum(messageMoney) value from message where DATE_SUB(CURDATE(), INTERVAL 1 ${date}) <= date(messageCreateDate) and messageLevel = #{level} and messageUserPhone = #{phone} group by messageBroker")
    ArrayList<HashMap<String,Object>> selectInOutGroupByBroker(@Param("phone") String phone,@Param("date")String date,@Param("level")Integer level);

    @Select("select messageType name,sum(messageMoney) value from message where DATE_SUB(CURDATE(), INTERVAL 1 ${date}) <= date(messageCreateDate) and messageLevel = 1 and  messageUserPhone = #{userphone} group by messageType")
    ArrayList<HashMap<String,Object>> selectIncomeGroupByTypeAndPhone(@Param("userphone") String phone,@Param("date")String date);

    @Select("select sum(messageMoney) value,messageType type from message where DATE_SUB(CURDATE(), INTERVAL 1 YEAR ) <= date(messageCreateDate) group by messageLevel")
    ArrayList<Integer> selectInAndOutcome();

    @Select("select sum(messageMoney) value,messageType type from message where DATE_SUB(CURDATE(), INTERVAL 1 YEAR ) <= date(messageCreateDate)  and  messageUserPhone = #{userphone} group by messageLevel")
    ArrayList<Integer> selectInAndOutcomeByPhone(@Param("userphone") String userphone);

    @Select("select messageLevel level,DATE_FORMAT(messageCreateDate,'%Y%m') months,messageType name,sum(messageMoney) value from message where DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(messageCreateDate)  group by messageType,months,messageLevel")
    ArrayList<HashMap<String,Object>> selectGroupByType();

    @Select("select max(messageMoney) from message where DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(messageCreateDate) and messageLevel = 1 ")
    Double selectMax();

    @Select("select distinct DATE_FORMAT(messageCreateDate,'%Y%m')  from message where DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(messageCreateDate) and  messageUserPhone = #{userphone} ")
    List<String> selectMonthByPhone(@Param("userphone") String userphone);

    @Select("select balance from userinfo where userTele = #{phone}")
    Double getBalance(String phone);



}